/*
 * Class to interact with Database
 * 
 */
package acc;

import com.sun.rowset.CachedRowSetImpl;
import entity.Server;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JComboBox;

/**
 *
 * @author Haitm
 */
public class DataInteraction {

    private static String servername = "";
    private static String port = "";
    private static String username = "";
    private static String password = "";
    private static String databaseName = "";
    private static String url = "";
    private static Connection conn = null;
    public static String message = "";

    /*
    Method to connect database
     */
    public static Connection connect() {
        try {
            //configServer();
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(url, username, password);
            message = "Connect to database successfully";

        } catch (SQLException ex) {
            ex.printStackTrace();
            message = "Database connecting failure.";
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
            message = "Database connecting failure.";
        } catch (Exception ex) {
            ex.printStackTrace();
            message = "Database connecting failure.";
        }

        return conn;
    }

    //Method to read Server Config from config file
    public static void configServer() {
        FileInputStream fi = null;
        ObjectInputStream oi = null;
        try {
            fi = new FileInputStream(Server.configFile);
            oi = new ObjectInputStream(fi);
            if (oi != null) {

                Server sv = (Server) oi.readObject();
                databaseName = sv.getDatabaseName();
                servername = sv.getServerName();
                port = sv.getPort();
                username = sv.getUserName();
                password = sv.getPassword();
                url = "jdbc:sqlserver://" + servername + ":" + port + ";databaseName=" + databaseName;
            } else {
                System.out.println("Server Config File is empty");
            }
            oi.close();
            fi.close();

        } catch (IOException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    //Method to create a cached rowset

    public static CachedRowSetImpl createCachedRowSet(String sql) {
        CachedRowSetImpl crs = null;
        try {
            crs = new CachedRowSetImpl();
            crs.setUsername(username);
            crs.setPassword(password);
            crs.setUrl(url);
            crs.setCommand(sql);
            int[] keys = {1};
            crs.setKeyColumns(keys);
            crs.execute(connect());
            message = "Success";
        } catch (SQLException ex) {
            ex.printStackTrace();
            message = "Fail";
        } finally {

            return crs;
        }
    }

    //Method to create a resultset
    public static ResultSet createResultSet(String sql) {
        ResultSet rs = null;
        try {
            Statement st = connect().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = st.executeQuery(sql);
            message = "Success";
        } catch (SQLException ex) {
            ex.printStackTrace();
            message = "Fail";
        } finally {
            return rs;
        }
    }

    //Method to get another field value from an exist field
    public static String getAnotherField(String table, String AvailableColumn, String WantedColumn, String Value) {
        String _sql = "select * from " + table ;
        String FieldName = "";
        ResultSet rs = DataInteraction.createResultSet(_sql);
        try {
            while (rs.next()) {
                if (Value.equals(rs.getString(AvailableColumn))) {
                    FieldName = rs.getString(WantedColumn);
                    break;
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            return FieldName;
        }
    }

    public static void readToComboBox(String table, JComboBox combo, String column) {
        String _sql = "select * from " + table;
        try {
            ResultSet rs = createResultSet(_sql);
            while (rs.next()) {
                String item = rs.getString(column);
                combo.addItem(item);

                message = "Read to combo box successfully";
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            message = "Read to combo box fail";
        }
    }

    public static int deleteRecordByID(String table, String column, String value) {
        return execQuery("delete from " + table + " where " + column + " like '" + value + "'");
    }

    // Method to execute a query by statement
    public static int execQuery(String sql) {
        int flag = 0;
        try {
            Statement st = connect().createStatement();
            flag = st.executeUpdate(sql);
            message = "Success";

        } catch (SQLException ex) {
            ex.printStackTrace();
            message = "Fail";
            flag = -1;
        } finally {
            return flag;
        }
    }
}
